/**
 * 
 */
package com.jm.utils;

import java.lang.reflect.Constructor;
import java.lang.reflect.Method;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;

import javax.activation.DataHandler;
import javax.activation.FileDataSource;
import javax.mail.Message;
import javax.mail.Multipart;
import javax.mail.Session;
import javax.mail.Transport;
import javax.mail.internet.InternetAddress;
import javax.mail.internet.MimeBodyPart;
import javax.mail.internet.MimeMessage;
import javax.mail.internet.MimeMultipart;
import javax.servlet.ServletRequest;
import javax.servlet.http.HttpServletRequest;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

import org.apache.commons.httpclient.NameValuePair;
import org.htmlparser.Parser;
import org.htmlparser.PrototypicalNodeFactory;
import org.htmlparser.util.ParserException;

import sun.misc.BASE64Encoder;

import com.JMAuthenticator;
import com.jm.utils.htmlNode.tag.DpTg;
import com.jm.utils.htmlNode.tag.ExportTg;
import com.jm.utils.htmlNode.tag.FieldTg;
import com.jm.utils.htmlNode.tag.FieldsetTg;
import com.jm.utils.htmlNode.tag.FontTg;
import com.jm.utils.htmlNode.tag.GroupTg;
import com.jm.utils.htmlNode.tag.KeepTg;
import com.jm.utils.htmlNode.tag.PreTg;
import com.jm.utils.htmlNode.tag.SpTg;
import com.jm.utils.htmlNode.tag.TBodyTg;
import com.jm.utils.htmlNode.tag.TfootTg;
import com.jm.utils.htmlNode.tag.ThTg;
import com.jm.utils.htmlNode.tag.TheadTg;
import com.jm.utils.htmlNode.tag.ToolbarTg;
import com.jm.utils.htmlNode.tag.UTg;
import com.jm.utils.xml.XmlUtils;
import com.jm.web.BaseMainPage;
import com.jm.web.BasePage;
import com.jm.web.data.BaseDataEntities;
import com.jm.web.data.BaseDataEntity;
import com.jm.web.data.JMForm;
import com.jm.web.data.Mail;
import com.jm.web.data.ReturnData;
import com.jm.web.data.toolbar.MenuOption;
import com.jm.web.form.Button;
import com.jm.web.form.Paging;

/**
 * @author yanjie
 *
 * 2011/10/8 下午4:02:35
 */
public class Utils extends common.Utils{
	public static final String TAG_RETDATA = "PAGE_RETDATA";//後臺頁面返回結果
	public static final String TAG_FORM = "PAGE_FORM";
	
	/**
	 * @param req
	 */
	public Utils(HttpServletRequest req) {
		super(req);
	}
	
	/**
	 * @param req
	 */
	public Utils(ServletRequest req) {
		super((HttpServletRequest)req);
	}
	
	public String getSubHr003Tsn(String hr003tsn,String strSpreate,String StrSplit,boolean bContainSelf) throws Exception{
		return getSubHr003Tsn(hr003tsn,strSpreate,StrSplit,bContainSelf,false);
	}
	/**
	 * @param hr003tsn 父部門tsn
	 * @param strSpreate tsn 外圍包含字符
	 * @param StrSplit 各個tsn之間的分隔符
	 * @param bContainSelf 是否包含自已
	 * @param bContainCancel 是否包已取消的部門
	 * @return
	 * 取子部門tsn
	 * @throws Exception 
	 */
	public String getSubHr003Tsn(String hr003tsn,String strSpreate,String StrSplit,boolean bContainSelf,boolean bContainCancel) throws Exception{
		if(StringHelper.IsNullOrEmpty(hr003tsn))
			return "";
		
		StringBuilderEx sb = new StringBuilderEx();
		boolean bFirst = true ;
		if(bContainSelf){
			sb.Append("%1$s%2$s%1$s",strSpreate,hr003tsn);
			bFirst = false ;
		}
		
        String sql = "";
        if(bContainCancel){
        	sql = StringHelper.Format("select tsn from hr..hr003 where lasttsn = '%1$s' ",hr003tsn);
        }else{
        	sql = StringHelper.Format("select tsn from hr..hr003 where lasttsn = '%1$s' and enab > 0",hr003tsn);
        }
        
        ResultSet rs = this.executeQuery("info3", sql);
            while (rs.next()) {
            	String strCurrentTsn = rs.getString("tsn");
            	if(!bFirst)
            		sb.Append(StrSplit);
            	else
            		bFirst = false ;
            	sb.Append("%1$s%2$s%1$s",strSpreate,strCurrentTsn);
                String strCurrentSubTsn =  this.getSubHr003Tsn(strCurrentTsn,strSpreate,StrSplit,false,bContainCancel);
                if(!StringHelper.IsNullOrEmpty(strCurrentSubTsn))
                	sb.Append("%1$s%2$s",StrSplit,strCurrentSubTsn);
            }
        rs.close();
		return sb.toString();
	}
	
	/**
	 * @return
	 * 返回後臺頁面執行的結果
	 */
	public ReturnData getReturnData(){
		Object object =  this.getRequest().getAttribute(TAG_RETDATA);
		if (object == null || !(object instanceof ReturnData)) {
			object = new ReturnData();
			((ReturnData)object).setUtils(this);
			this.getRequest().setAttribute(TAG_RETDATA, object);
		}
		
		return (ReturnData)object;
	}
	
	/**
	 * 绑定表单数据
	 */
	public void bindForm(){
		JMForm form = getForm();
		if(!form.isBind())
			form.BindForm(this.getRequest());
	}
	
	/**
	 * @return
	 * 获取表单数据
	 */
	public JMForm getForm(){
		Object form =  this.getRequest().getAttribute(TAG_FORM);
		if (form == null || !(form instanceof JMForm)) {
			form = new JMForm();
			((JMForm)form).setUtils(this);
			//绑定表单数据
			if(!((JMForm)form).isBind()){
				((JMForm)form).BindForm(this.getRequest());
			}
			this.getRequest().setAttribute(TAG_FORM, form);
		}
		return (JMForm)form;
	}
	
	@Override
	public boolean isAuthorized(String sysCode) throws Exception {
		if(this.getRequest() != null && this.getRequest().getAttribute(sysCode) != null)
			return (Boolean)this.getRequest().getAttribute(sysCode);
		
		boolean bAdmin = super.isAuthorized(sysCode);
		this.getRequest().setAttribute(sysCode, bAdmin);
		return bAdmin;
	}
	
	 public static String formatXML(String strOrigin){
		 return formatXML(strOrigin,true);
	 }
	/**
     * 分别用 &amp;、&lt; 和 &gt; 替换字符 &、< 和 >。
     * 用数字字符实体（&#0; 到 &#0x1F）替换范围 0x-0x1F 中（不包括空白字符 0x9、0x10 和 0x13）的字符值。
     * 如果在属性值的上下文中调用 WriteString，则分别用 &quot; 和 &apos; 替换双引号和单引号。
     * @param strOrigin String
     * @param bProperty boolean
     * @return String
     */
    public static String formatXML(String strOrigin,boolean bProperty)
    {
        strOrigin = strOrigin.replace("&","&amp;");
        strOrigin = strOrigin.replace("<","&lt;");
        strOrigin = strOrigin.replace(">","&gt;");

        if(bProperty)
        {
        	strOrigin = strOrigin.replace("\n","&#xA;");
        	strOrigin = strOrigin.replace("\r","&#xD;");
            strOrigin = strOrigin.replace("\"","&quot;");
            strOrigin = strOrigin.replace("'","&apos;");
        }

        return strOrigin;
    }
    
    /**
     * @param map
     * @return
     */
    public static String MapToMenuString(Map map){
    	if(map == null || map.size() ==0)
    		return "";
    	
    	JSONArray jsonArray = MapToJsonArray(map);
    	if(jsonArray == null)
    		return "";
    	
    	return jsonArray.toString().replace("\"", "'");
    }
    
    /**
     * @param strMenu
     * @return
     */
    public static Map MenuStringToMap(String strMenu){
    	if(StringHelper.IsNullOrEmpty(strMenu))
    		return null;
    	
    	JSONArray jsonArray = JSONArray.fromString(strMenu);
    	if(jsonArray == null || jsonArray.length() == 0)
    		return null;
    	
    	return JsonArrayToMap(jsonArray);
    }
    
    /**
     * @param jsonArray
     * @return
     */
    public static Map  JsonArrayToMap(JSONArray jsonArray){
    	Map map = new LinkedHashMap();
    	for (int i = 0; i < jsonArray.length(); i++) {
    		Object obj = jsonArray.get(i);
    		if(obj instanceof JSONObject){
    			JSONObject json = (JSONObject)obj;
    			String key =(String)json.keys().next();
    			Object subObj = json.get(key);
    			if(subObj instanceof String){
    				map.put(key, (String)subObj);
    			}else if(subObj instanceof JSONArray){
    				Map subMap = JsonArrayToMap((JSONArray)subObj);
    				if(subMap != null && subMap.size() > 0)
    				map.put(key,subMap);
    			}else if(subObj instanceof JSONObject){
    				MenuOption option = (MenuOption)JSONObject.toBean((JSONObject)subObj,MenuOption.class);
    				map.put(key,option);
    			}
    		}
		}
    	
    	return map;
    }
    
    /**
     * @param map
     * @return
     */
    public static JSONArray  MapToJsonArray(Map map){
    	if(map == null || map.size() == 0)
    		return null;
    	
    	JSONArray jsonArray = new JSONArray();
    	for (Iterator iter = map.keySet().iterator() ;iter.hasNext();) {
			String key = (String)iter.next();
			Object obj = map.get(key); 
			if(obj instanceof String){
				JSONObject json = new JSONObject();
				json.put(key, (String)obj);
				jsonArray.put(json);
			}else if(obj instanceof Map){
				JSONObject json = new JSONObject();
				JSONArray subJson = MapToJsonArray((Map)obj);
				if(subJson != null){
					json.put(key, subJson);
					jsonArray.put(json);
				}
			}else if(obj instanceof MenuOption){
				JSONObject json = new JSONObject();
				json.put(key, JSONObject.fromBean(obj));
				jsonArray.put(json);
			}
    	}
    	
    	return jsonArray;
    }
    
    /**
     * @param option
     * @return
     */
    public static String MapToMenuHtml(Map option,Button button){
		if(option == null || option.size() == 0)
			return "";
		
		StringBuilderEx sb = new StringBuilderEx();
		sb.Append("\r\n <ul>");
//		if(button != null){
//			sb.Append("\r\n <li><a href=\"#\" value=\"\" >%1$s</a></li>",button.getValue());
//		}
		StringBuilderEx js = new StringBuilderEx();
		
		for (Iterator iter = option.keySet().iterator(); iter.hasNext();) {
			String key = (String)iter.next();
			Object object = option.get(key);
			
			MenuOption menuOption = null;
			if(object instanceof String){
				String strValue = (String)object;
				if(strValue.startsWith("{")){
					JSONObject json = JSONObject.fromString(strValue);
					menuOption = (MenuOption)JSONObject.toBean(json,MenuOption.class);
					sb.Append("\r\n <li><a href=\"#\" %1$s >%2$s</a>",menuOption.ToOutputString(),key);
				}else{
					sb.Append("\r\n <li><a href=\"#\" value=\"%1$s\" >%2$s</a>",strValue,key);
				}
			}else if(object instanceof MenuOption){
				menuOption = (MenuOption)object;
				sb.Append("\r\n <li><a href=\"#\" %1$s >%2$s</a>",menuOption.ToOutputString(),key);
			}else if(object instanceof Map){
				sb.Append("\r\n <li><a href=\"#\" value=\"%1$s\" >%2$s</a>","",key);
				sb.Append(MapToMenuHtml((Map)object,null));
			}
			sb.Append("</li>");
			
			if(button != null && menuOption != null && "true".equalsIgnoreCase(menuOption.getConfirm())){
				js.Append("$P.confirm['%1$s-%2$s']='%3$s';",button.getName(),menuOption.getConfirmText(),key);
			}
		}
		sb.Append("\r\n </ul>");
		
		if(js.toString().length() >0)
			button.getBaseTag().getPage().RegisterOnReadyScript(BasePage.SCRIPTRUN_ONREADY,js.toString());
		return sb.toString();
	}
    
    /**
     * @param strConfig
     * @param json
     * @return
     */
    public static String getConfigFormatString(String strConfig,JSONObject json){
    	if(StringHelper.IsNullOrEmpty(strConfig) || json.isEmpty())
    		return null;
    	
		JSONObject config = JSONObject.fromString(strConfig);
		String formmat = config.getString("format");
		String params[] = config.getString("params").split(",");
		String values[] = new String[params.length];
		for (int j = 0; j < params.length; j++) {
			try{
				values[j] = StringHelper.getValue(json.getString(params[j]));
			}catch(Exception e){
				values[j] = "";
			}
		}
		
		return StringHelper.Format(formmat,values);
	}
    
    /* (non-Javadoc)
     * @see common.Utils#i18n(java.lang.String)
     */
    public String i18n(String str){
    	try {
			return super.i18n(str);
		} catch (Exception e) {
			return str;
		}
    }
    
    /**
     * @param key
     * @return
     * 從session中取值
     */
    public Object getsv(String key){
    	if(this.getSession() == null)
    		return null;
    	return this.getSession().getAttribute(key);
    }
    
    /**
     * @param mail
     * 發送郵件
     */
    public void sendMail(Mail mail)throws Exception {
    	Session session = null;
    	if(mail == null)
    		return ;	
    	
    	if(mail.getSubject().length() > 100) {
    		mail.setSubject(mail.getSubject().substring(0, 100));
    	}
    	String from = "";
    	if(!StringHelper.IsNullOrEmpty(mail.getFrom())){
    		from = mail.getFrom();
    	}else{
    		from = "exchgadmin@mail.JMKNIT.com";
    	}
        BASE64Encoder enc = new BASE64Encoder();
        Properties props = new Properties();
        if(true && (mail.isAuth() || this.isAtuMail(mail.getTo()))){
	        props.put("mail.smtp.host", "mail.jmknit.com"); 
	        props.put("mail.smtp.auth","true");
//	        props.put("mail.debug","true");
//	        props.put("mail.stmp.sendpartial", "true");
	        session = Session.getInstance(props, new JMAuthenticator());
        }else{
        	props.put("mail.smtp.host", "webmail.jmknit.com"); // mail host.
        	session = Session.getDefaultInstance(props, null);
        }
        
        MimeMessage message = new MimeMessage(session);
        message.setFrom(new InternetAddress(from));//發信人
        message.setRecipients(Message.RecipientType.TO, mail.getTo());//收件人
        if(!StringHelper.IsNullOrEmpty(mail.getCc())){
        	message.setRecipients(Message.RecipientType.CC, mail.getCc());//副本收件人
        }
        if(!StringHelper.IsNullOrEmpty(mail.getBcc())){
            message.setRecipients(Message.RecipientType.BCC, mail.getBcc());//暗送人即發郵件給此人，但收件人和副本中看不到此人
        }
        
        if(mail.isEncode()){
        	message.setSubject("=?big5?B?" + enc.encode(mail.getSubject().getBytes()) + "?=");
        }else{
        	message.setSubject(mail.getSubject());
        }
        
        Multipart multiPart = new MimeMultipart();
        MimeBodyPart bodyPart = new MimeBodyPart();
        if (mail.getContentType() == Mail.URL) {
            // str 如果是傳入 url要先經過處理.
            String[] x = mail.getBody().split("\\?");
            String url = x[0]; // 網址.
            NameValuePair[] nvPair = new NameValuePair[0];
            if (x.length > 1) { // 有帶參數. 湊成 NameValuePair[].
                String[] y = x[1].split("&");
                nvPair = new NameValuePair[y.length];
                for (int i = 0; i < y.length; i++) {
                    String[] z = y[i].split("=");
                    if (z.length > 1) {
                        nvPair[i] = new NameValuePair(z[0], z[1]);
                    } else {
                        nvPair[i] = new NameValuePair(z[0], "");
                    }
                }
            }
            String content = mail.getResponse(url, Mail.GET, nvPair);
            bodyPart.setContent(content, "text/html;charset=utf-8");
        } else if (mail.getContentType() == Mail.TEXT) {
            bodyPart.setText(mail.getBody());
        } else if (mail.getContentType() == Mail.HTML) {
        	bodyPart.setContent(mail.getBody(), "text/html;charset=utf-8");
        }
        
        if(!StringHelper.IsNullOrEmpty(mail.getFileName()) && mail.getFile() != null){
        	MimeBodyPart mbpFile = new MimeBodyPart();
        	mbpFile.setFileName(mail.getFileName());
        	mbpFile.setDataHandler(new DataHandler(new FileDataSource(mail.getFile())));
        	multiPart.addBodyPart(mbpFile);
        }
        
        multiPart.addBodyPart(bodyPart);
        message.setContent(multiPart);
        message.saveChanges();
        
        Transport.send(message);
    }
    
    /**
     * @return
     */
    public static Parser getParserInstance(){
    	Parser parser = new Parser();
		try {
			parser.setEncoding("UTF-8");
		} catch (ParserException e) {
			e.printStackTrace();
		}
		PrototypicalNodeFactory factory = new PrototypicalNodeFactory();  
		factory.registerTag(new GroupTg());
		factory.registerTag(new DpTg());
		factory.registerTag(new FieldTg());
		factory.registerTag(new FieldsetTg());
		factory.registerTag(new SpTg());
		factory.registerTag(new ToolbarTg());
		factory.registerTag(new TheadTg());
		factory.registerTag(new TBodyTg());
		factory.registerTag(new TfootTg());
		factory.registerTag(new KeepTg());
		factory.registerTag(new ExportTg());
		factory.registerTag(new ThTg());
		factory.registerTag(new UTg());
		factory.registerTag(new FontTg());
		factory.registerTag(new PreTg());
		parser.setNodeFactory(factory);
		return parser ;
    }
    
    public BaseMainPage getPage(){
    	if(this.getRequest() == null)
    		return null;
		return (BaseMainPage)this.getRequest().getAttribute(BasePage.TAG_CURRENT_PAGE);
	}
    
    public ResultSet executePagingQuery(String gridid,String serverName, String sql, String uniqueColumn) throws Exception {
    	ReturnData ret = getReturnData();
    	int[] pagingData = this.getPagingData(serverName, sql);
		int totalPage = (int) Math.ceil(pagingData[0] / (double) pagingData[1]); // 共幾頁.
		int thisPage = this.getForm().gpiv(gridid+"_page");
		thisPage = (thisPage < 1 && thisPage != -1) ? 1 : thisPage;
		Paging paging = new Paging();
		paging.setGridid(gridid);
		paging.setTotalRows(pagingData[0]);
		paging.setPageSize(pagingData[1]);
		paging.setTotalPage(totalPage);
		paging.setCurrentPage(thisPage);
		ret.SetParamValue(StringHelper.Format("paging_%1$s",gridid),paging);
		if(paging.getCurrentPage() >= 0){
			return super.executePagingQuery(serverName,sql,uniqueColumn);
		}else{
			return this.executeQuery(serverName,sql);
		}
    }
    
    /**
     * @return 数据文件物理地址
     */
    public String getDataPath() {
    	String realpath = this.getRequest().getRealPath("");
    	StringBuffer filePath = new StringBuffer(realpath.substring(0, realpath.lastIndexOf("\\")));
    	
    	filePath.append("\\userData\\config\\data\\");
        return filePath.toString();
	}
    
    /**
     * @return 临时数据文件物理地址
     */
    public String getTempDataPath() {
		return this.getTempDataPath(true);
	}
    
    /**
     * @param bpageinfo
     * @return 临时数据文件物理地址
     */
    public String getTempDataPath(boolean bpageinfo){
    	String realpath = this.getRequest().getRealPath("");
    	StringBuffer filePath = new StringBuffer(realpath.substring(0, realpath.lastIndexOf("\\")));
    	
    	filePath.append("\\userData\\config\\tempdata\\");
        filePath.append(this.getPage().getPageName().replace(".jsp","")).append("\\");
        return filePath.toString();
    }
    
    public BaseDataEntities executePagingQuery3(String gridid,String serverName, String sql,int pageSize) throws Exception {
    	ReturnData ret = getReturnData();
    	int[] pagingData = this.getPagingData2(serverName, sql);
    	if(pageSize <= 0)
    		pageSize = pagingData[1];
    		
		int totalPage = (int) Math.ceil(pagingData[0] / (double) pageSize); // 共幾頁.
		int thisPage = this.getForm().gpiv(gridid+"_page");
		thisPage = (thisPage < 1 && thisPage != -1) ? 1 : thisPage;
		Paging paging = new Paging();
		paging.setGridid(gridid);
		paging.setTotalRows(pagingData[0]);
		paging.setPageSize(pageSize);
		paging.setTotalPage(totalPage);
		paging.setCurrentPage(thisPage);
		ret.SetParamValue(StringHelper.Format("paging_%1$s",gridid),paging);
		//String strPagingSql = this.parsePagingSql2(sql,paging);
		//if(StringHelper.IsNullOrEmpty(strPagingSql))return null;
		
        StringBuffer fileName = new StringBuffer(this.getTempDataPath());
        fileName.append(gridid).append(this.getHrNum()).append(".xml");
        XmlUtils xml = new XmlUtils(fileName.toString());
        if(this.getForm().gpiv(gridid+"_page") == 0 && !this.getPage().isExport()){
        	//重新生成xml
	        xml.write(this.executeQuery(serverName,sql));
        }
    	return xml.getDataEntities(paging);
    }
    
    public ResultSet executePagingQuery2(String gridid,String serverName, String sql) throws Exception {
    	return executePagingQuery2(gridid,serverName,sql,0);
    }
    
    public ResultSet executePagingQuery2(String gridid,String serverName, String sql,int pageSize) throws Exception {
    	ReturnData ret = getReturnData();
    	int[] pagingData = this.getPagingData2(serverName, sql);
    	if(pageSize <= 0)
    		pageSize = pagingData[1];
    		
		int totalPage = (int) Math.ceil(pagingData[0] / (double) pageSize); // 共幾頁.
		int thisPage = this.getForm().gpiv(gridid+"_page");
		thisPage = (thisPage < 1 && thisPage != -1) ? 1 : thisPage;
		Paging paging = new Paging();
		paging.setGridid(gridid);
		paging.setTotalRows(pagingData[0]);
		paging.setPageSize(pageSize);
		paging.setTotalPage(totalPage);
		paging.setCurrentPage(thisPage);
		ret.SetParamValue(StringHelper.Format("paging_%1$s",gridid),paging);
		
		String strPagingSql ="";
		if(paging.getCurrentPage() >= 0){
			strPagingSql = this.parsePagingSql2(sql,paging);
		}else{
			strPagingSql = sql;
		}
		if(StringHelper.IsNullOrEmpty(strPagingSql))return null;
    	return this.executeQuery(serverName,strPagingSql);
    }
    
    /**
     * utils:page 需要查詢的相關資料. 
     */
    private int[] getPagingData2(String serverName, String str) throws Exception {
        int[] result = new int[2];
        String[] pagingSql = this.splitPagingSql2(str);
        String num = pagingSql[0];
        String showColumn = pagingSql[1];
        String fromWhere = pagingSql[2];
        String groupBy = pagingSql[3];
        StringBuffer sb = new StringBuffer();
        // 查詢句裡面有沒有 group by 做法不太一樣.
        if (groupBy.length() != 0) {
            sb.append("select count(*) as total from (select ").append(showColumn).append(" from ").append(fromWhere).append(" group by ").append(groupBy).append(") as totalRecordsForPaging");
        } else {
            sb.append("select count(*) as total from ").append(fromWhere);
        }
        ResultSet rs = this.executeQuery(serverName, sb.toString());
        rs.next();
        result[0] = rs.getInt(1); // 取的總筆數
        result[1] = Integer.parseInt(num); // 取得一頁有幾個筆資料
        return result;
    }
    
    private String parsePagingSql2(String sql, Paging paging) {
    	String[] pagingSql = this.splitPagingSql2(sql);
        String num = pagingSql[0];
        String showColumn = pagingSql[1];
        String fromWhere = pagingSql[2];
        String groupBy = pagingSql[3];
        String orderBy = pagingSql[4];
        if(StringHelper.IsNullOrEmpty(orderBy)){
        	return null;
        }
        
        StringBuffer sb = new StringBuffer();
        sb.append("select * from ( ");
        sb.append("select ROW_NUMBER() over(order by ").append(orderBy).append(") AS rownum, ");
        sb.append(showColumn);
        sb.append(" from ");
        sb.append(fromWhere);
        if (groupBy.length() != 0) { // 查詢句 有沒有 group by 處理方式不一樣.
            sb.append(" group by ").append(groupBy);
        }
        sb.append(" ) a ");
        sb.append(" where a.rownum > ").append((paging.getCurrentPage()-1)*paging.getPageSize());
        sb.append(" and a.rownum <= ").append((paging.getCurrentPage())*paging.getPageSize());
        return sb.toString();
    }
    
    /**
     * 拆解查詢SQL. 
     */
    private String[] splitPagingSql2(String str) {
        String[] result = { "30", "", "", "", "" }; // {預設顯示筆數, showColumn, fromWhere, groupBy, orderBy}
        // 1. 資料筆數 = top 的筆數, 預設 30 筆. 
        String sql = str.toLowerCase().replaceFirst("select", "").replaceAll("\\(", " \\( ").replaceAll("\\)", " \\) ").trim();
        if (sql.startsWith("top")) { // 有 top 就取得數字. 
            sql = sql.replaceFirst("top", "").trim();
            result[0] = sql.split(" ")[0];
            sql = sql.replaceFirst(result[0], "").trim();
        }
        // 2. 要顯示的 column : 第一個不在()之內 的from 之前的字串都是.
        String[] array = this.splitIgnoreSpecial2(sql, "from");
        result[1] = array[0];
        sql = array[1];
        array = this.splitIgnoreSpecial2(sql, "group");
        if (array[1].length() == 0) { // 此 sql 沒有包含 group by
            array = this.splitIgnoreSpecial2(sql, "order");
            result[2] = array[0];
            result[4] = array[1].replaceFirst("by", "").trim();
        } else { // 有 group by
            result[2] = array[0];
            sql = array[1].replaceFirst("by", "").trim();
            array = this.splitIgnoreSpecial2(sql, "order");
            result[3] = array[0];
            result[4] = array[1].replaceFirst("by", "").trim();
        }
        if (result[4].length() == 0) {
            System.out.print("- 分頁查詢沒有order by資料 : ");
            System.out.println(str);
        }
        return result;
    }

    /**
     * 拆解SQL需要用的到一個方法.
     */
    private String[] splitIgnoreSpecial2(String str, String split) {
        split = new StringBuffer().append(" ").append(split).append(" ").toString();
        // 找到第一個 split 分隔字, 且不包含在 ( ) 之內.  
        String[] result = new String[2];
        int index = 0;
        int len = str.length();
        while (true) {
            index = str.indexOf(split, index);
            index = (index == -1) ? len : index;
            result[0] = str.substring(0, index).trim();
            int next = (index + split.length() > len) ? len : index + split.length();
            result[1] = str.substring(next, len).trim();
            // 左右兩邊的   ' 要成對的, 且 ()個數必須相同.
            if (result[0].replaceAll("[^\\(]", "").length() == result[0].replaceAll("[^\\)]", "").length() && result[1].replaceAll("[^\\(]", "").length() == result[1].replaceAll("[^\\)]", "").length() && result[0].replaceAll("[^']", "").length() % 2 == 0 && result[1].replaceAll("[^']", "").length() % 2 == 0) {
                break;
            } else {
                index++;
            }
        }
        return result;
    }
    
    /**
     * @return ip
     */
    public String getRemoteAddr(){
    	if(this.getRequest() ==null)
    		return "";
    	return this.getRequest().getRemoteAddr();
    }
    
    public String getPrgName(){
    	if(this.getRequest() ==null)
    		return "";
    	return this.getRequest().getRequestURI().toString();
    }
    
    public String getSessionEmail(){
    	return StringHelper.getValue(this.getSession().getAttribute("email"));
    }
    
    public String getSessionUIType(){
    	return StringHelper.getValue(this.getSession().getAttribute("uitype"));
    }
    
    /**
     * @return 
     * 返回global對像
     */
    public GlobalHelper getGlobalHelper(){
    	if(this.getPage() != null){
    		return (GlobalHelper)this.getPage().getPageContext().getServletContext().getAttribute(GlobalHelper.TAG_GlobalHelper);
    	}else if(this.getRequest() != null){
    		return (GlobalHelper)this.getRequest().getSession().getServletContext().getAttribute(GlobalHelper.TAG_GlobalHelper);
    	}
    	return null;
    }
    
    /**
     * @param group
     * @param row
     * @return
     * 合并單元格中使用  取分組的值
     * @throws Exception 
     */
    public String getGroupValue(String group ,Object row) {
    	if(StringHelper.IsNullOrEmpty(group) || row == null){
    		return "";
    	}
    	
    	List list = new ArrayList();
    	String format = "";
    	int size = 0;
    	for (int i = 0; i < group.split(",").length; i++) {
			String temp = group.split(",")[i];
			if(!StringHelper.IsNullOrEmpty(temp)){
				size ++;
				if(size >1){
					format += "--";
				}
				
				if(row instanceof ResultSet){
					try {
						list.add(this.getRsString((ResultSet)row,temp));
					} catch (Exception e) {
						e.printStackTrace();
					}
				}else if(row instanceof Map){
					list.add(String.valueOf(((Map)row).get(temp)));
				}else if(row instanceof BaseDataEntity){
					list.add(((BaseDataEntity)row).gpsv(temp));
				}
				format += "%"+size+"$s";
			}
		}
    	
    	if(list.size() > 0 && list.size() == size){
    		String params[] = new String[size];
    		list.toArray(params);
    		return StringHelper.Format(format,params);
    	}
    	return "";
    } 
    
    /**   
     * 新建实例   
     *   
     * @param className 类名   
     * @param args 构造函数的参数   
     * @return 新建的实例   
     * @throws Exception   
     */   

	public static Object NewInstance(String className, Object[] args) {
		Class newoneClass = null ;
		try {
			newoneClass = Class.forName(className);
			Class[] argsClass = new Class[args.length];

			for (int i = 0, j = args.length; i < j; i++) {
				argsClass[i] = args[i].getClass();
			}

			Constructor cons = newoneClass.getConstructor(argsClass);

			return cons.newInstance(args);
		} catch (Exception e) {
			return null;
		}
	}
 	
 	/**   
      * 执行某对象方法   
      *   
      * @param owner 对象   
      * @param methodName 方法名   
      * @param args 参数   
      * @return 方法返回值   
      * @throws Exception   
      */ 
	public static Object InvokeMethod(Object owner, String methodName,
			Object[] args) {
		Class ownerClass = null ;
		try {
			ownerClass = owner.getClass();
			Class[] argsClass = new Class[args.length];

			for (int i = 0, j = args.length; i < j; i++) {
				if(args[i].getClass().toString().equals("class java.lang.Integer")){
					argsClass[i]=int.class;
				} else if(args[i].getClass().toString().equals("class java.lang.Long")){ 
					argsClass[i]=long.class;
				} else if(args[i].getClass().toString().equals("class java.lang.Double")){ 
					argsClass[i]=double.class;
				}else if(args[i].getClass().toString().equals("class java.lang.Boolean")){ 
					argsClass[i]=boolean.class;
				}else if(args[i].getClass().toString().equals("class java.lang.Float")){ 
					argsClass[i]=float.class;
				}else{
					argsClass[i] = args[i].getClass();
				}
			}
			Method method = getMethod(ownerClass,methodName, argsClass);

			return method.invoke(owner, args);
		} catch (Exception e) {
			System.out.println(StringHelper.Format("錯誤:%1$s", e.getMessage()));
			return null;
		}
	}
	
	public static Method getMethod(Class ownerClass,String methodName,Class[] argsClass) throws Exception{
		Method method = null ;
		try{
			method = ownerClass.getMethod(methodName, argsClass);
		}catch (Exception e) {
			if(ownerClass.getClass().getName().equals(Object.class.getName())){
				throw new Exception(e.getMessage()) ;
			}else{
				Method[] ms = ownerClass.getMethods();
				if(ms != null)
					for (int i = 0; i < ms.length; i++) {
						Method m = ms[i];
						Class[] paramsType = (m.getParameterTypes());
						boolean isOK = true ;
						if(paramsType!= null && paramsType.length == argsClass.length){
							for (int j = 0; j < paramsType.length; j++) {
								isOK = paramsType[j].isAssignableFrom(argsClass[j]);
								if(!isOK)
									break;
							}
							if(isOK)
								return m;
						}
						
					}
				getMethod(ownerClass.getSuperclass(), methodName, argsClass);
			}	
		}
		
		return method ;
	}
	
	@Override
	public String getChName() {
	    return StringHelper.getValue((String) this.getSession().getAttribute("chname"));// 中文名稱
	}
}

;